SQL DBA Tutorial

Welcome to the SQL DBA  Tutorials. The intent of these tutorials is to provide in depth understanding of SQL DBA . In addition to free SQL DBA  Tutorials, you can find interview questions, how to tutorials and issues and their resolutions.

Introduction

Database Administrators (DBAs) are responsible for the design, implementation, support and maintenance of computerized databases in today's organizations. The role also includes architecting, building and scaling databases for future data growth and capacity. They are also responsible for security, performance and availability of data to users and customers. .

The SQL Server Database system from Microsoft Corporation has gained popularity over the last years, and it is believed to be one of the few surviving relational database systems in this marketplace (the others being Oracle and DB2 from IBM).

An important part of many DBAs’ and SQL Server administrators’ jobs is keeping databases current. Doing so involves adding, changing, and deleting data from tables. T-SQL makes these tasks easy. You just need to use INSERT, UPDATE, and DELETE statements. Before I show you how to use these statements, there are a few things you need to do before proceeding.

Assuming that you have the permissions needed to create databases and tables and to issue datamodification statements, you can create MyDB, MyTable, and the dummy tables with the MyDB .sql and MyTable.sql files. You can download these files by going to Download the Code hotlink icon above. After you’ve downloaded and unzipped the 98105.zip file, open a query window in either SQL Server 2005’s SQL Server Management Studio (SSMS) or SQL Server 2000’s Query Analyzer and copy the code in MyDB.sql into the window. In the two FILENAME entries, replace C:\Program Files Microsoft SQL Server\MSSQL.1\MSSQL\DATA MyDB.mdf with a valid path on your server. Run the query to create MyDB. To create MyTable and the dummy tables, copy the code in MyTable.sql into the query window and execute it. Before working with data in any table, you should familiarize yourself with the table’s layout. So, open MyTable and do so.

Inserting a Single Record

A basic INSERT statement that adds a single record (aka row) to a table consists of three parts and looks like INSERT INTO Part1 (Part2) VALUES (Part3) Part 1 is where you specify the target table that will hold the new record. Part2 is where you list the names of the columns in the target table for which you have data. You need to enclose the list in parentheses and use commas to separate column names. Part 3 is where you provide the data to be inserted. When you want to specify the actual data values, you use the VALUES parameter followed by those values. You need to enclose the list of data values in parentheses and separate the values with commas. When a value is character based—such as a string or a datetime value—you also need to enclose it in single quotes (' '). For example, running the following statement inserts the data values of Testable and 2007-09-22 into MyTable ObjectName and Creation- Date columns, respectively: INSERT INTO MyTable (ObjectName, CreationDate) VALUES ('TestTable', '2007-09-22') If you familiarized yourself with MyTable earlier, you probably noticed that  specified two of the three columns in this INSERT statement. The first column is an identity column, which has a special meaning. The data for that column is automatically added and its values incremented with each record being inserted. This is a powerful feature of SQL Server that you should be aware of. To view the newly inserted record, execute the code SELECT * FROM MyTable

Inserting the Results from a SELECT Statement

In “T-SQL 101, Lesson 1” (March 2008, InstantDoc ID 97724), I showed you how to get data out of a database with a SELECT statement. You can use the INSERT statement to store the data retrieved by the SELECT statement in a table. Like the single-record INSERT statement, the INSERT statement that stores the results of a SELECT statement has three parts: the target database (Part1), the column names (Part2), and the data (Part3). However, instead of using the VALUES parameter to specify the actual data values in Part3, you use a SELECT statement that retrieves the data from another source. For example, when run against the MyDB database, the query INSERT INTO MyTable (ObjectName, CreationDate) SELECT name, crdate FROM sysobjects WHERE TYPE = 'U' ORDER BY name inserts into our new table MyTable records that contain the name and creation date of all the user-defined tables in MyDB, ordered alphabetically by their names. So, assuming you ran the single-record INSERT statement previously discussed, your results from this query should look like those in Figure 2, with two exceptions. First, the Creation Date values for the MyTable and dummy tables will contain the date and time when you ran MyTable.sql. Second, the ObjectName column will be wider. (I shortened it for space reasons.) Note that Test Table is still the first record. The ORDER BY name clause only applies to the new records that the SELECT statement is inserting. Test Table was added previously with the single-record INSERT statement. You can use any SELECT statement you like, as long as the data types of the columns listed in Part2 of the INSERT statement match those columns specified in the SELECT statement. This opens the door for capturing all sorts of data. Use your newfound knowledge with caution, however. You don’t want to be inserting millions of records at a time on a busy server or on a server with limited disk space.

Updating Data

Now that you have a handle on inserting data, let’s look at how the UPDATE statement works. A simple UPDATE statement typically consists of three parts:

Part1 is where you specify the target table. Part2 is where you specify the columns that are to be changed, along with the new data for each column. Part3 is optional but in most cases essential. This is where you specify a filter using the WHERE clause. If you don’t specify a WHERE clause, you’ll update every single record in the table. For example, the query

updates the CreationDate value for each record in MyTable, as Figure 3 shows. In Figure 3, note that the CreationDate value is 2007-09-23 00:00:00.000 and not 2007-09-23 as specified in the query. Because the data type of the CreationDate column is defined as datetime and the query doesn’t specify a time portion, SQL Server assumes you mean midnight and adds 00:00:00.000. Now let’s use a WHERE clause to modify the ObjectName and CreationDate columns for a specific record: UPDATE MyTable SET ObjectName = 'PartyTime', CreationDate = '1999-12-31 23:00:00’ WHERE TableID = 1 only the first record (specified by WHERE TableID = 1) is updated with the new table name of PartyTime and a new creation date of 1999-12-31 23:00:00.

Deleting Data

The command that every DBA fears will fall into the wrong hands is the DELETE statement. Although it can be misused, it serves an important role when using queries to modify data in tables. A basic DELETE statement typically consists of two parts: DELETE Part1 WHERE Part2 Part1 is where you specify the target table. Part2 is where you specify a filter using the WHERE clause. Like the WHERE clause in an UPDATE statement, the WHERE clause in a DELETE statement is optional but usually essential. If you don’t include a WHERE clause and filter, you’ll delete all the records in the specified table. Let’s say you want to delete the records with a creation date older than September 22, 2007 from MyTables. The query

will do just that,. You need to be careful when modifying data with either the DELETE or UPDATE statement. Always include a WHERE clause unless you’re positive you want to affect all the records in a table. If you’re unsure of how many records you’ll be affecting, try running a SELECT statement with the same WHERE clause.

New Features in SQL Server 2016

SQL Server 2016 is the latest iteration of Microsoft’s flagship database and analytics platform with lots of new features and improved existing features making it an intelligent database and analytics platform for a mobile first, cloud first world. In fact, my first impression of SQL Server 2016 Community Technology Preview 2 is that it is another exciting launch SQL Server product that offers plenty of new features and improved existing features that enable large organizations to deliver outstanding performance for mission-critical applications and deeper insights on the organization’s data across on-premises and cloud.